package com.busdepot.webapp.domain;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Component;

import com.busdepot.webapp.util.DbUtils;

@Component("shippingRepository")
public class ShippingRepositoryImpl implements ShippingRepository {
	private SimpleJdbcTemplate simpleJdbcTemplate = DbUtils
			.getSimpleJdbcTemplate();

	public double getCodCharge() {
		String sql = "select cod from shippingfees where id=1";
		List<CodCharge> codList = simpleJdbcTemplate
				.query(sql, new CodMapper());
		CodCharge codCharge = codList.get(0);
		return codCharge.getCod();
	}

	public double getHandlingCharge() {
		String sql = "select handling from shippingfees where id=1";
		List<Handling> handlingList = simpleJdbcTemplate.query(sql,
				new HandlingMapper());
		Handling handling = handlingList.get(0);
		return handling.getHandling();
	}

	public double getShippingRate(int shippingRegion, int shippingType) {
		String sql = "select percentage from shippingrate where shippingregionid=? and shippingtypeid=?";
		List<ShippingRate> shippingRateList = simpleJdbcTemplate.query(sql,
				new ShippingRateMapper(), shippingRegion, shippingType);
		ShippingRate shippingRate = shippingRateList.get(0);
		return shippingRate.getShippingRate();
	}

	public List<ShippingRegion> getShippingRegion() {
		String sql = "select startzip, endzip, shippingregionid from regionlookup";
		return this.simpleJdbcTemplate.query(sql, new ShippingRegionMapper());
	}

	public double getCategorySurcharge(int partId) {
		String sql = "select c.surcharge from partnumber p, vendor v, categories c, link l where l.category_id=c.id and l.partnumber_id=p.id and p.id=?";
		List<Surcharge> surchargeList = simpleJdbcTemplate.query(sql,
				new CategorySurchargeMapper(), partId);
		if (surchargeList.size() == 0) {
			return 0;
		}
		Surcharge surcharge = surchargeList.get(0);
		return surcharge.getCategorySurcharge();
	}

	public double getPartSurcharge(int partId) {
		String sql = "select p.surcharge from partnumber p where p.id=?";
		List<Surcharge> surchargeList = simpleJdbcTemplate.query(sql,
				new PartSurchargeMapper(), partId);
		if (surchargeList.size() == 0) {
			return 0;
		}
		Surcharge surcharge = surchargeList.get(0);
		return surcharge.getPartSurcharge();
	}

	public double getVendorSurcharge(int vendorId) {
		String sql = "select v.surcharge from vendor v where v.id=?";
		List<Surcharge> surchargeList = simpleJdbcTemplate.query(sql,
				new VendorSurchargeMapper(), vendorId);
		if (surchargeList.size() == 0) {
			return 0;
		}
		Surcharge surcharge = surchargeList.get(0);
		return surcharge.getVendorSurcharge();
	}

	private static final class ShippingRegionMapper implements RowMapper {
		public ShippingRegion mapRow(ResultSet rs, int rowNum)
				throws SQLException {
			int startZip = rs.getInt("startzip");
			int endZip = rs.getInt("endzip");
			int region = rs.getInt("shippingregionid");
			ShippingRegion shippingRegion = new ShippingRegion(startZip,
					endZip, region);
			return shippingRegion;
		}
	};

	private static final class CodMapper implements RowMapper {
		public CodCharge mapRow(ResultSet rs, int rowNum) throws SQLException {
			double cod = rs.getDouble("COD");
			CodCharge codCharge = new CodCharge(cod);
			return codCharge;
		}
	};

	private static final class CategorySurchargeMapper implements RowMapper {
		public Surcharge mapRow(ResultSet rs, int rowNum) throws SQLException {
			double categorySurcharge = rs.getDouble("c.surcharge");
			double partSurcharge = 0;
			double vendorSurcharge = 0;
			Surcharge surcharge = new Surcharge(partSurcharge, vendorSurcharge,
					categorySurcharge);
			return surcharge;
		}
	};

	private static final class PartSurchargeMapper implements RowMapper {
		public Surcharge mapRow(ResultSet rs, int rowNum) throws SQLException {
			double categorySurcharge = 0;
			double partSurcharge = rs.getDouble("p.surcharge");
			double vendorSurcharge = 0;
			Surcharge surcharge = new Surcharge(partSurcharge, vendorSurcharge,
					categorySurcharge);
			return surcharge;
		}
	};

	private static final class VendorSurchargeMapper implements RowMapper {
		public Surcharge mapRow(ResultSet rs, int rowNum) throws SQLException {
			double categorySurcharge = 0;
			double partSurcharge = 0;
			double vendorSurcharge = rs.getDouble("v.surcharge");
			Surcharge surcharge = new Surcharge(partSurcharge, vendorSurcharge,
					categorySurcharge);
			return surcharge;
		}
	};

	private static final class HandlingMapper implements RowMapper {
		public Handling mapRow(ResultSet rs, int rowNum) throws SQLException {
			double handleCharge = rs.getDouble("handling");
			Handling handling = new Handling(handleCharge);
			return handling;
		}
	};

	private static final class ShippingRateMapper implements RowMapper {
		public ShippingRate mapRow(ResultSet rs, int rowNum)
				throws SQLException {
			double rate = rs.getDouble("percentage");
			ShippingRate shippingRate = new ShippingRate(rate);
			return shippingRate;
		}
	};

}
